/*
 * Created on 2004-12-7
 *
 * ��ݿ�t�Ӷ��󣬵���ģʽ�����ڴ���ݿ�t�ӳ�������ݿ�t�ӣ������·�װ��ݿ�t�Ӷ���ķ�����
 */
package com.tss.util;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

public class DbConn {
	public static final String NullConnection = "The Connection is Null!";
	public static final String NotPreparedStatement = "You Forget to Set PreparedStatement!";
	public static final String RsFieldNotFound = "The Rs Field &name is not found!";
	private int ConnectionSocketErr = 0;
	public Connection conn = null; 
	protected PreparedStatement pstmt = null; 
	private Vector queryResult = null;
	public DbConn() {
		this.conn = DbConnectionManager.getConnection();
		
	}
	
	public void prepare(String sql) 
		throws SQLException 
	{
		Log.trace(sql);
		if (conn == null || conn.isClosed())
			throw new SQLException(NullConnection);
		try {
			if (pstmt != null) pstmt.close();
		}
		catch (Throwable e) {}
		try {
			pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			//pstmt = conn.prepareStatement(sql);
		}
		catch (SQLException e) {
			throw e;
		}
	}
	
	public void setObject(int index, Object parameter) 
		throws SQLException 
	{
		Log.trace("index=" + index + ",parameter=" + parameter);
		String paramVal = "";
		if (! (parameter instanceof java.sql.Date)) {
			parameter = (parameter == null) ? "" : parameter.toString().trim();
		}
		paramVal=(String)parameter;
		pstmt.setObject(index,paramVal);
	}
	
	public void setString(int index, String parameter) 
		throws SQLException 
	{
		Log.trace("index=" + index + ",parameter=" + parameter);
		parameter = parameter == null ? "" : parameter.trim();
		pstmt.setString(index, parameter);
	}
	
	public void setLong(int index, long parameter) 
		throws SQLException 
	{
		Log.trace("index=" + index + ",parameter=" + parameter);
		pstmt.setLong(index,parameter);
	}
		
	public void setInt(int index, int parameter) 
		throws SQLException 
	{
		Log.trace("index=" + index + ",parameter=" + parameter);
		pstmt.setInt(index, parameter);
	}
	public void setBoolean(int index, boolean parameter) 
	throws SQLException 
{
	Log.trace("index=" + index + ",parameter=" + parameter);
	pstmt.setBoolean(index, parameter);
}
	public void setDouble(int index, double parameter) 
		throws SQLException 
	{
		Log.trace("index=" + index + ",parameter=" + parameter);
		pstmt.setDouble(index, parameter);
	}
	
	public void setDate(int index, Date parameter) 
		throws SQLException 
    {
		Log.trace("index=" + index + ",parameter=" + parameter);
		pstmt.setDate(index, parameter);
    }
	
	public DbRs executeQuery(String sql) 
		throws SQLException
	{
	    Log.trace(sql);
		if (conn == null || conn.isClosed())
			throw new SQLException(NullConnection);
		ResultSet rs = null;
		DbRs vResult = null;
		try {
			Statement stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			vResult = resultset2Vector(rs);
			rs.close();
			stmt.close();
		}
		catch (SQLException e) {
			throw e;
		}
		return vResult;
	}
	
	public DbRs executeQuery(int pageNo, int pageSize) 
		throws SQLException 
	{
		ResultSet rs = null;
		DbRs vResult = null;
		try {
			if (pstmt == null)
				throw new SQLException(NotPreparedStatement);
			rs = pstmt.executeQuery();
			vResult = resultset2Vector(rs, pageNo, pageSize);
		}
		catch (SQLException e) {
			throw e;
		}
		finally {
			try {
				rs.close();
				closeStmt();
			}
			catch (Throwable e1) {
				Log.trace(e1+"");
			}
		}
		Log.trace(vResult+"");
		return vResult;
	}

	public DbRs executeQuery() 
		throws SQLException
	{
		ResultSet rs = null;
		DbRs vResult = null;
		try {
			if (pstmt == null)
				throw new SQLException(NotPreparedStatement);
			rs = pstmt.executeQuery();
			vResult = resultset2Vector(rs);
		}
		catch (SQLException e) {
			throw e;
		}
		finally {
			try {
				rs.close();
				closeStmt();
			}
			catch (Throwable e1) {
				Log.trace(e1+"");
			}
		}
		Log.trace(vResult+"");
		return vResult;
	}

	public int executeUpdate(String sql) 
		throws SQLException 
	{
		if (conn == null || conn.isClosed())
			throw new SQLException(NullConnection);
		try {
			Statement stmt = conn.createStatement();
			return stmt.executeUpdate(sql);
		}
		catch (SQLException e) {
			throw e;
		}
	}

	public int executeUpdate() throws SQLException {
		try {
			if (pstmt == null)
			throw new SQLException(NotPreparedStatement);
			return pstmt.executeUpdate();
		}
		catch (SQLException e) {
			throw e;
		}
	}

	public void close() {
		closeStmt();
		try {
			if (conn != null) {
				conn.setAutoCommit(true);
				conn.close();
			}
		}
		catch (Throwable e) {}
		conn = null;
	}

	public void setAutoCommit(boolean bAutoCommit) 
		throws SQLException
	{
		if (conn == null ) //|| conn.isClosed())
			throw new SQLException(NotPreparedStatement);
		try {
			conn.setAutoCommit(bAutoCommit);
		}
		catch (SQLException e) {
			throw e;
		}
	}

	public void commit() 
		throws SQLException 
	{
		if (conn == null ) // || conn.isClosed())
			throw new SQLException(NotPreparedStatement);
		try {
			conn.commit();
		}
		catch (SQLException e) {
			throw e;
		}
	}

	public void rollback() 
		throws SQLException
	{
		if (conn == null ) // || conn.isClosed())
			throw new SQLException(NotPreparedStatement);
		try {
			conn.rollback();
		}
		catch (SQLException e) {
			throw e;
		}
	}

	private void closeStmt() 
	{
		try {
			pstmt.close();
		}
		catch (Throwable e) {}
		pstmt = null;
	}

	private DbRs resultset2Vector(ResultSet rs) throws SQLException 
	{
		ResultSetMetaData meta = rs.getMetaData();
		int columnCount = meta.getColumnCount();
		DbRs vResult = new DbRs();
		boolean flag = true;
		while (rs.next()) {
			java.util.Vector vTemp = new Vector();
			for (int i = 0; i < columnCount; i++) {
				if (flag) {
					vResult.put(meta.getColumnName(i + 1).toUpperCase(), String.valueOf(i));
				}
				Object oTemp = rs.getObject(i + 1);
				vTemp.addElement(oTemp);
			}
			vResult.addElement(vTemp);
			flag = false;
		}
		rs.close();
		if (!vResult.isEmpty())
		vResult.printColumnInfo();
		return vResult;
	}

	private DbRs resultset2Vector(ResultSet rs, int pageNo, int pageSize) throws SQLException 
	{
		ResultSetMetaData meta = rs.getMetaData();
		int columnCount = meta.getColumnCount();
		DbRs vResult = new DbRs();
		if (!rs.next()) {
			vResult.setInfo("total","0");
			return vResult;
		}
		
		boolean flag = true;
		int rsNums = 0;
		int startIndex = (pageNo - 1) * pageSize + 1;
		if (startIndex<0) startIndex = 1;
	
		rs.absolute(startIndex);
		for (int i=0; i<pageSize; i++) {
			if (!rs.isAfterLast()){
				Vector vTemp = new Vector();
				for (int j = 0; j < columnCount; j++) {
					if (flag) {
						vResult.put(meta.getColumnName(j + 1).toUpperCase(),j+"");
					}
					Object oTemp = rs.getObject(j + 1);
					//String sTemp = (oTemp == null) ? "" : oTemp.toString();
					vTemp.addElement(oTemp);
				}
				vResult.addElement(vTemp);
				flag = false;
				rs.next();
			}
		}
		rs.close();
		if (!vResult.isEmpty())
			vResult.printColumnInfo();
		return vResult;
	}

	public boolean isEmpty() {
		return queryResult.size() == 0;
	}
}
